package com.tros.admin.validation;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.tros.customer.notification.MailNotification;

/**
 * Servlet implementation class ChqReqValidation
 */
@WebServlet("/ChqReqValidation")
public class ChqReqValidation extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ChqReqValidation() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		 Connection connect =null;			 
		 HttpSession ses = request.getSession();
	
		 String Chqno = request.getParameter("name");
		
		 
			try{
			       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			       connect =DriverManager.getConnection("jdbc:odbc:bank","root", " ");
		           System.out.println("DB Connection created :"+connect);
		           
		            if(request.getParameter("approve")!=null)
					 {				
		        	   System.out.println("---------------> came admin here 2--------->");
						 UpdateDepositAmt(connect,Chqno,ses);
						 droptableinfodecline(connect,Chqno);
						 response.sendRedirect("perDepo.jsp"); 
				         
					 }else{
						 System.out.println("---------------> came admin here 3--------->");
						 droptableinfodecline(connect,Chqno);
						 response.sendRedirect("perDepo.jsp"); 
						 try{
								
								String email = "";
								String lname = "";
								
								PreparedStatement state=connect.prepareStatement("SELECT * FROM EXISTUSERS WHERE "+"CUSTOMERID="+ses.getAttribute("currentID"));
								ResultSet result=state.executeQuery();
								
								while(result.next()){
									
								      email=result.getString("EMAIL");
								      lname=result.getString("LASTNAME");
								}
								
								state.close();
								
								MailNotification mail = new MailNotification(email);
							    
							    String text = "Hello "+
							    		      "\n\n Your Deposit through Cheque Was Denied by Admin "+
							    		      "\n\n ChequeNO : "+Chqno+							    		   
							    		      "\n\n Note : Please do not reply to this E-Mail Notification";
							    
							    mail.sendMail( "Cheque Deposit", text);
								connect.close();
								
							}catch(Exception e){
								e.printStackTrace();
								System.out.println(" -----------> Failed in Mail notification --------");
							}
					 }
			}
			catch(Exception e)
			{ 
				System.out.println("+++++++++++ admin user"+e.getMessage());
		       	e.printStackTrace();
			}
			
	}
	
	private void UpdateDepositAmt(Connection connect, String Chqno,
			HttpSession ses) {
		// TODO Auto-generated method stub
		
		   String AccNO = null;
		    String AccType=null;
		    String ChQ_Amt=null;
		    String custid=null;
			
			try
			{
				PreparedStatement state=connect.prepareStatement("SELECT * FROM CHQREQ WHERE CHQ_NO=?");
				state.setString(1,Chqno);
				ResultSet result=state.executeQuery();
				
				while(result.next()){
					
				     AccNO=result.getString("ACC_NO");
				     AccType=result.getString("ACC_TYPE");
				     ChQ_Amt=result.getString("CHQ_AMOUNT");
				     custid=result.getString("CUSTOMERID");
				}
				state.close();
				
			}catch(Exception e){
				System.out.println("---------> error here cust id---->"+e.getMessage());
			}
			
			try{
				if(AccNO != null)
				{
					updateExistUsers(connect,custid,AccNO,AccType,ChQ_Amt,ses);				
				}		    
				
			}catch(Exception e){
				
				System.out.println("---------> error here drop id---->"+e.getMessage());
			}
		
	}

	private void updateExistUsers(Connection connect, String custId,
			String accNO, String accType, String chQ_Amt, HttpSession ses) {
		// TODO Auto-generated method stub
		
		
		 try{	
			 
			Double Bal = null;
			 
			PreparedStatement state;

		    if(accType.equals("Savings"))
		    {
		    	 state=connect.prepareStatement("SELECT * FROM SAVACC WHERE CUSTOMERID=?");
		    }else
		    {
		    	 state=connect.prepareStatement("SELECT * FROM CHCKACC WHERE CUSTOMERID=?");
		    }
		    
			state.setString(1,custId);
			ResultSet result=state.executeQuery();

			while(result.next()){
				
			     Bal =Double.parseDouble(result.getString("ACC_BALANCE"));		    
			}
			
			result.close();
			state.close();
		   
		    Double Amount = Bal + Double.parseDouble(chQ_Amt);
		    
		    PreparedStatement state1;
			
			  if(accType.equals("Savings")){				  
				  state1=connect.prepareStatement( "UPDATE SAVACC SET ACC_BALANCE=? WHERE "+"CUSTOMERID="+custId);            
			  }else
			  {				  
				   state1=connect.prepareStatement( "UPDATE CHCKACC SET ACC_BALANCE=? WHERE "+"CUSTOMERID="+custId);	           
			  }
			  
			state1.setString(1,String.valueOf(Amount));
	   	    state1.executeUpdate();
	   	    state1.close();

		    System.out.println("Came here ==============>End ");	
			
		}catch(Exception e){
			System.out.println("update table--------->"+e.getMessage());
		}
		 
		 try{
				
				String email = "";
				String lname = "";
				
				PreparedStatement state=connect.prepareStatement("SELECT * FROM EXISTUSERS WHERE "+"CUSTOMERID="+ses.getAttribute("currentID"));
				ResultSet result=state.executeQuery();
				
				while(result.next()){
					
				      email=result.getString("EMAIL");
				      lname=result.getString("LASTNAME");
				}
				
				state.close();
				
				MailNotification mail = new MailNotification(email);
			    
			    String text = "Hello "+
			    		      "\n\n Your Deposit through Cheque Was Credited to Your Account "+
			    		      "\n\n CustomerID : "+custId+
			    		      "\n Cheque Amount : "+chQ_Amt+
			    		      "\n\n Note : Please do not reply to this E-Mail Notification";
			    
			    mail.sendMail( "Cheque Deposit", text);
				connect.close();
				
			}catch(Exception e){
				e.printStackTrace();
				System.out.println(" -----------> Failed in Mail notification --------");
			}
			
        	
	}

	private void droptableinfodecline(Connection connect, String custID) {
		// TODO Auto-generated method stub
		
		try{
			
			PreparedStatement state=connect.prepareStatement("DELETE FROM CHQREQ WHERE CHQ_NO=?");
			state.setString(1,custID);
			state.executeUpdate();	
			state.close();
			
		}catch(Exception e){
			System.out.println("Drop table--------->"+e.getMessage());
		}
	}

}
